﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using TAMS;

namespace TAMS
{
    public partial class editOffice : System.Web.UI.Page
    {
        MySqlConnection conn;
        MySqlCommand sqlCommand;
        MySqlDataReader readVals;
        string strProvider = Resource1.databaseConnection;
        string type;
        string id;
        string enteredName;

        protected void Page_Load(object sender, EventArgs e)
        {
            // Connect to the Database
            conn = new MySqlConnection(strProvider);
            conn.Open();
            string query;

            // Get the type
            type = Request.QueryString["type"];
            id = Request.QueryString["id"];
            enteredName = Request.QueryString["name"];

            if (!IsPostBack)
            {
                if (type == "staff")
                {
                    string getQuery = "SELECT* FROM Office O, Staff S, FWorks FW" +
                            " WHERE S.fsid = FW.fsid AND O.offcode = FW.offcode AND S.fsid=" + id;

                    query = "SELECT O.offcode as 'Office Code', O.o_building as 'Building Name', O.o_telno as 'Phone Number' FROM Office O" +
                            " WHERE O.offcode NOT IN (SELECT offcode FROM Staff S, FWorks FW" +
                            " WHERE S.fsid = FW.fsid )";



                    sqlCommand = new MySqlCommand(getQuery, conn);
                    readVals = sqlCommand.ExecuteReader();
                    readVals.Read();



                    Label1.Text = "Office info for, id: " + id + " and Name: " + enteredName;

                    if (readVals.HasRows)
                    {
                        Label2.Text = "" + readVals.GetString("offcode");
                        Label3.Text = "" + readVals.GetString("o_building");
                        Label4.Text = "" + readVals.GetString("o_telno");
                        Label5.Text = "" + readVals.GetDateTime("fw_since").ToString("dd-MM-yyyy");
                    }
                    else
                    {
                        Label2.Text = "" + "No Data";
                        Label3.Text = "" + "" + "No Data";
                        Label4.Text = "" + "" + "No Data";
                        Label5.Text = "" + "" + "No Data";
                    }


                    readVals.Close();

                    

                    sqlCommand = new MySqlCommand(query, conn);
                    readVals = sqlCommand.ExecuteReader();

                    GridView1.DataSource = readVals;
                    GridView1.DataBind();
 

                }
                else if (type == "student")
                {
                    string getQuery = "SELECT* FROM Office O, GradStudent G, SWorks SW" +
                           " WHERE G.sid = SW.sid AND O.offcode = SW.offcode AND G.sid=" + id;

                    query = "SELECT O.offcode as 'Office Code', O.o_building as 'Building Name', O.o_telno as 'Phone Number' FROM Office O" +
                            " WHERE O.offcode NOT IN (SELECT offcode FROM GradStudent G, SWorks SW" +
                            " WHERE G.sid = SW.sid )";


                    sqlCommand = new MySqlCommand(getQuery, conn);
                    readVals = sqlCommand.ExecuteReader();
                    readVals.Read();


                    Label1.Text = "Office info for, id: " + id + " and Name: " + enteredName;

                    if (readVals.HasRows)
                    {
                        Label2.Text = "" + readVals.GetString("offcode");
                        Label3.Text = "" + readVals.GetString("o_building");
                        Label4.Text = "" + readVals.GetString("o_telNo");
                        Label5.Text = "" + readVals.GetDateTime("sw_since").ToString("dd-MM-yyyy");
                    }
                    else
                    {
                        Label2.Text = "" + "No Data";
                        Label3.Text = "" + "" + "No Data";
                        Label4.Text = "" + "" + "No Data";
                        Label5.Text = "" + "" + "No Data";
                    }


                    readVals.Close();

                    sqlCommand = new MySqlCommand(query, conn);
                    readVals = sqlCommand.ExecuteReader();

                    GridView1.DataSource = readVals;
                    GridView1.DataBind();
                }

            }


        }

        protected void change_clicked(object sender, EventArgs e)
        {
            string query;

            GridViewRow clickedRow = ((Button)sender).NamingContainer as GridViewRow;

            // id yi alıyor cell in 1. indexi
            string key = clickedRow.Cells[1].Text;

            if (type == "staff")
            {
                string date = (DateTime.Now).ToString("yyyy-MM-dd");

                string getQuery = "SELECT* FROM Office O, Staff S, FWorks FW" +
                        " WHERE S.fsid = FW.fsid AND O.offcode = FW.offcode AND S.fsid=" + id;

                sqlCommand = new MySqlCommand(getQuery, conn);
                readVals = sqlCommand.ExecuteReader();
                readVals.Read();

                if (readVals.HasRows)
                {
                    query = query = "UPDATE FWorks "
                                    + "SET  offcode=" + "'" + key + "' ," + " fw_since=" + "'" + date + "'"
                                    + " WHERE fsid=" + id;
                }
                else
                {
                    query = "INSERT  "
                            + "INTO  FWorks"
                            + " VALUES(" + "'" + id + "', " + "'"+ key+"'" + ", " + "'" + date + "'"+")";
                }
                readVals.Close();


                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editOffice.aspx?" + "name=" + enteredName + "&type=staff&id=" + id;

                Response.Redirect(url);

            }
            else if (type == "student")
            {
                string date = (DateTime.Now).ToString("yyyy-MM-dd");

                string getQuery = "SELECT* FROM Office O, GradStudent G, SWorks SW" +
                       " WHERE G.sid = SW.sid AND O.offcode = SW.offcode AND G.sid=" + id;

                sqlCommand = new MySqlCommand(getQuery, conn);
                readVals = sqlCommand.ExecuteReader();
                readVals.Read();

                if (readVals.HasRows)
                {
                    query = "UPDATE SWorks "
                            + "SET  offcode=" + "'" + key + "' ," + " sw_since=" + "'" + date + "'"
                            + " WHERE sid=" + id;
                }
                else
                {
                    query = "INSERT  "
                             + "INTO  SWorks"
                             + " VALUES(" + "'" + id + "', " + "'" + key + "'" + ", " + "'" + date + "'" + ")";
                }
                readVals.Close();

                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editOffice.aspx?" + "name=" + enteredName + "&type=student&id=" + id;

                Response.Redirect(url);
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            if (type == "staff")
            {
                string query =  "DELETE  "
                                + "FROM  FWorks"
                                + " WHERE fsid=" + id;
           
                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editOffice.aspx?" + "name=" + enteredName + "&type=staff&id=" + id;

                Response.Redirect(url);
            }
            else if(type == "student")
            {
                string query = "DELETE  "
                + "FROM  SWorks"
                + " WHERE sid=" + id;

                sqlCommand = new MySqlCommand(query, conn);
                sqlCommand.ExecuteNonQuery();

                string url = "editOffice.aspx?" + "name=" + enteredName + "&type=student&id=" + id;

                Response.Redirect(url);
            }
        }
    }
}